package com.apestech.framework.sql;

import com.apestech.framework.db.SqlSession;
import com.apestech.framework.dictionary.modul.ImportedKey;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.condition.ConditionMap;
import com.apestech.framework.sql.lib.TableSql;
import com.apestech.framework.sql.modul.FromList;
import com.apestech.framework.sql.modul.SqlList;
import com.apestech.framework.sql.variable.SqlSection;
import com.apestech.framework.util.StringFuncUtil;
import com.apestech.framework.util.callback.SpiltStrCallback;
import com.apestech.framework.xml.plugin.XmlPaste;
import com.apestech.framework.xml.sql.SqlNodeVar;

import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author hup
 * @create 2018-09-01-9:19
 */
public class SqlUtil extends StringFuncUtil {

    public class MathField{
        private String param1;
        private String param2;

        public MathField(String param1, String param2) throws Exception {
            this.param1=param1.toLowerCase().trim();
            if(param2==null) this.param2=null;
            else this.param2=param2.toLowerCase().trim();
        }

        public String getParam1() {
            return param1;
        }

        public String getParam2() {
            return param2;
        }
    }

    public class From{
        private String alias;
        private String tab;
        private String user;

        public From(String tab, String alias){
            this.tab=tab.trim();
            this.alias=alias.trim();
            if("(".equals(leftStr(this.tab,1)))
                this.tab=this.tab.substring(1,this.tab.length()-1);
            init();
        }

        private void init(){
            int pos=tab.indexOf("#");
            while(pos>=0){
                int tpos=tab.indexOf("}");
                tab=tab.substring(0,pos)+"?"+tab.substring(tpos+1, tab.length());
                pos=tab.indexOf("#");
            }
        }

        public String getAlias() {
            return alias;
        }

        public String getTab() {
            return tab;
        }
    }

    public class Type {
        private String type;
        private Integer length;
        private Integer size;
        private String subType;

        public Type(String type, String length, String size,  String subType) throws Exception {
            this.type=type.trim();
            if(length!=null) this.length=castToInt(length.trim());
            if(size!=null) this.size=castToInt(size.trim());
            if(subType!=null) this.subType=subType.trim();
        }

        public String getType() {
            return type;
        }

        public Integer getLength() {
            return length;
        }

        public Integer getSize() {
            return size;
        }

        public String getSubType() {
            return subType;
        }
    }

    public class ExecTable{
        private String tabname;
        private String keyWhere;

        public ExecTable(String tabname, String keyWhere){
            this.tabname=tabname;
            this.keyWhere=keyWhere;
        }

        public String getTabname() {
            return tabname;
        }

        public String getKeyWhere() {
            return keyWhere;
        }
    }

    public String addSql(FromList fromtab, String text, ConditionMap input, SqlList param, String sql) throws Exception {
        text=finiSql(text);
        SqlSection sqlvar=new SqlSection(fromtab, input);
        sqlvar.doVar(" "+text);
        param.add(sqlvar.getParam());
        if(!leftStr(text,1).equals("'") && countString(text,"'") / 2==0){
            sql=sql+" "+text;
        } else {
            if(text.indexOf("LEFT ")==0){
                sql=sql+" ";
            }
            sql=sql+text;
        }
        return sql;
    }

    public MathField getMathField(String text) throws Exception {
        int r=text.indexOf("(");
        int l=text.indexOf(")");
        if(r>=0 && l>=0 && r<l){
            String express=text.substring(0, r).trim();
            String length=text.substring(r+1, l).trim();
            return new MathField(express, length);
        } else return new MathField(text, null);
    }

    public ArrayList<From> getForm(String fromStr){
        if(fromStr==null) return null;
        fromStr=fromStr.trim();
        int pos=fromStr.toLowerCase().indexOf("select");
        if(pos<0){
            if(fromStr.indexOf(" ")>=0){
                return getBlandFrom(fromStr);
            } else if(fromStr.indexOf(".")>=0){
                return getSemicolonFrom(fromStr);
            } else {
                ArrayList<From> from=new ArrayList<From>();
                from.add(new From(fromStr,""));
                return from;
            }
        } else {
            String a="";
            String s=fromStr;
            ArrayList<From> from=new ArrayList<From>();
            if(!")".equals(rightStr(fromStr,1))){
                a=fromStr.substring(fromStr.lastIndexOf(" "), fromStr.length()).trim();
                s=s.substring(s.indexOf("(")+1, s.lastIndexOf(")"));
            }
            from.add(new From(s,a));
            return from;
        }
    }

    private ArrayList<From> getUserFrom(String fromStr){
        ArrayList<From> from=new ArrayList<From>();
        //from u.tab a, tab b      a.u.tab;b.tab
        return from;
    }

    private ArrayList<From> getBlandFrom(String fromStr){
        ArrayList<From> from=new ArrayList<From>();
        String alias=null;
        String sql;
        int pos;
        if(fromStr.indexOf(",")>=0){
            String[] list=fromStr.split(",");
            int len=list.length;
            for(int i=0; i<len; i++){
                String s=list[i].trim();
                if(!"".equals(s)){
                    pos=s.indexOf(" ");
                    if(pos>=0) {
                        sql = s.substring(0, pos).trim();
                        alias = s.substring(pos + 1, s.length()).trim();
                    } else {
                        sql=s.trim();
                        alias="";
                    }
                    from.add(new From(sql, alias));
                }
            }
        } else {
            pos=fromStr.indexOf(" ");
            if(pos>=0){
                sql=fromStr.substring(0, pos).trim();
                alias=fromStr.substring(pos+1, fromStr.length()).trim();
            } else {
                sql=fromStr.trim();
                alias="";
            }
            from.add(new From(sql, alias));
        }
        return from;
    }

    private ArrayList<From> getSemicolonFrom(String fromStr){
        ArrayList<From> from=new ArrayList<From>();
        String alias=null;
        String sql;
        int pos;
        if(fromStr.indexOf(";")>=0){
            String[] list=fromStr.split(";");
            int len=list.length;
            for(int i=0; i<len; i++){
                String s=list[i].trim();
                if(!"".equals(s)){
                    pos=s.indexOf(".");
                    if(pos>=0) {
                        alias=s.substring(0, pos).trim();
                        sql=s.substring(pos+1, s.length()).trim();
                    } else {
                        sql=s.trim();
                        alias="";
                    }
                    from.add(new From(sql, alias));
                }
            }
        } else {
            pos=fromStr.indexOf(".");
            if(pos>=0){
                alias=fromStr.substring(0, pos).trim();
                sql=fromStr.substring(pos+1, fromStr.length()).trim();
            } else {
                sql=fromStr.trim();
                alias="";
            }
            from.add(new From(sql, alias));
        }
        return from;
    }

    public String getSelectSql(JSONObject m) throws Exception {
        String nbsql=m.getString("nb_sql");
        String ffield=m.getString("field");
        String ftable=m.getString("table");
        String fwhere=m.getString("where");
        String fqueryField=m.getString("queryField");
        if(nbsql==null && ffield!=null && ftable!=null && fqueryField!=null){
            //<SUPPLIER_ID queryField="Y2.NAME" field="Y1.ID" table="Y1.MAS_PARTY_ROLE;Y2.MAS_PARTY" where="Y1.PARTY_ID=Y2.ID" alaisField="SUPPLIER_NAME" />
            String qfield="Y1."+fqueryField;
            String qfrom=ftable+" Y1";
            String qwhere="#{KEY}=Y1."+ffield;
            if(ftable.indexOf(".")>=0 || ftable.indexOf(";")>=0){
                ArrayList<From> tablist = getForm(ftable);
                if(tablist!=null){
                    int len=tablist.size();
                    if(len>0) qfrom="";
                    if(len>1){
                        if(fqueryField.indexOf(".")<0) throw new Exception("多表关联子查询时，返回字段["+fqueryField+"]必须包括别名信息！");
                        if(ffield.indexOf(".")<0) throw new Exception("多表关联子查询时，与主表关联字段["+ffield+"]必须包括别名信息！");
                    }
                    qfield=fqueryField;
                    qwhere="#{KEY}="+ffield;

                    for(int i=0; i<len; i++){
                        String alais=tablist.get(i).getAlias();
                        String tab=tablist.get(i).getTab();
                        if(len>1 && (alais==null || "".equals(alais))) throw new Exception("多表关联子查询时，请记录对应表["+tab+"]所用的别名！");
                        if(!"".equals(qfrom)) qfrom=qfrom+",";
                        qfrom=qfrom+tab+" "+alais;
                    }
                }
            }
            nbsql="SELECT "+qfield+" FROM "+qfrom+" WHERE 1=1 AND "+qwhere;
        }
        if(nbsql!=null && fwhere!=null) {
            nbsql=nbsql+" AND "+fwhere;
        }
        return nbsql;
    }

    public JSONObject getKeyValue(SqlSession session, String mtabName, String tabName, JSONObject data) throws Exception {
        JSONObject m=new JSONObject();
        Table tab=session.getDictTable(tabName);
        if(tab==null){
            return null;
        }
        String fkey=null;
        String key=null;
        if(tab.getPkFKey()!=null){
            ImportedKey.ImportedField fk=tab.getPkFKey().getFieldByTable(mtabName);
            if(fk==null) return null;
            fkey=fk.getPkColumnName();
            key=fk.getColumnName();
        } else {
            tab=session.getDictTable(mtabName);
            fkey=tab.getPkKey().getPkColumn();
            fkey=fkey.replace(";",",");
            key=fkey;
        }
        if(key!=null && !"".equals(key)){
            if(key.indexOf(",")<0) m.put(key, data.get(fkey));
            else {
                String[] list=key.split(",");
                String[] flist=fkey.split(",");
                int len=list.length;
                for(int i=0; i<len; i++){
                    if(!"".equals(list[i])){
                        m.put(list[i], data.get(flist[i]));
                    }
                }
            }
            return m;
        } else return null;
    }

    public JSONObject getKeyValue(SqlSession session, String tabName, JSONObject data) throws Exception {
        JSONObject m=new JSONObject();
        Table tab=session.getDictTable(tabName);
        String key=tab.getPkKey().getPkColumn();
        key=key.replaceAll(";",",");
        execSplitStr(key, ",", new SpiltStrCallback(){
            @Override
            public void exec(String id) throws Exception {
                if(!"".equals(id)) m.put(id,data.get(id));
            }
        });
        return m;
    }

    public Type getColumnType(String type) throws Exception {
        type=type.trim();
        String stype=type;
        String length=null;
        String size=null;
        String subType=null;
        int pos=type.indexOf("(");
        if(pos>=0){
            stype=type.substring(0, pos);
            int dpos=type.indexOf(",");
            if(dpos>=0){
                length=type.substring(pos+1, dpos);
                size=type.substring(dpos+1,type.length()-1);
            } else {
                dpos=type.indexOf(" ");
                if(dpos>=0){
                    length=type.substring(pos+1, dpos);
                    subType=type.substring(dpos+1,type.length()-1);
                }
                else length=type.substring(pos+1, type.length()-1);
            }
        }
        return new Type(stype, length, size, subType);
    }

    public String getColumnSql(String sql){
        String str=sql;
        int pos=str.indexOf("#{");
        while(pos>=0){
            int end=str.indexOf("}");
            String s=str.substring(pos, end+1);
            str=str.replace(s, "?");
            pos=str.indexOf("#{", pos);
        }
        return str;
    }

    public static String iniSql(String sql){
        sql = sql.replace("<>", " BDY ");
        sql = sql.replace(">=", " DYDY ");
        sql = sql.replace(" > ", " DY ");
        sql = sql.replace("<=", " XYDY ");
        sql = sql.replace(" < ", " XY ");
        return sql;
    }

    public static String finiSql(String sql){
        sql = sql.replace( " DY ", " > ");
        sql = sql.replace(" DYDY ", " >= ");
        sql = sql.replace(" XY ", " < " );
        sql = sql.replace(" XYDY ", " <= ");
        sql = sql.replace(" BDY ", " <> ");
        return sql;
    }

    public static ConditionMap getQueryInfo(String queryId) throws Exception {
        JSONObject qparam=new JSONObject();
        qparam.put("queryid",queryId);
        ConditionMap param=new ConditionMap(qparam);
        setXmlId(queryId, "select", param);
        return param;
    }

    private static void setXmlId(String xmlId, String nodetype, ConditionMap param) throws Exception {
        SqlNodeVar.SqlType type=SqlNodeVar.SqlType.XML;
        XmlPaste xml=new XmlPaste(xmlId, nodetype, (SqlSession)null);
        xml.expain();
        String xmlsql=xml.getExecSql(param);
        param.setRule(xml.getRule());
        param.setRole(xml.getAuths());
        param.setHtw(xml.getHtw());
        if(xml.getHtw()!=null){
            if(xml.getHtw().getGridHead()==null) {
                xml.getHtw().setGridHead(xml.getHead());
            }
            param.setHtw(xml.getHtw());
        }
        else param.setHead(xml.getHead());
        param.setSqlrule(xml.getSqlrule());
        param.setOtherControl(xml.getOther());
        param.setXmlSql(xmlsql);
        if(xml.getAutoCompletion()!=null) param.setAutoCompletion(xml.getAutoCompletion());
    }

    public static String getNoWhereSql(String sql){
        String nsql=sql;
        String key=leftStr(SqlNodeVar.getWhereStr(), SqlNodeVar.getWhereStr().length()-1);
        String rkey = null;
        int pos=nsql.indexOf(key);
        while(pos>=0){
            if(nsql.indexOf(SqlNodeVar.getWhereStr())>=0){
                nsql=nsql.replace(SqlNodeVar.getWhereStr(), "");
            } else {
                rkey=getWhereKey(nsql, pos);
                int len=nsql.length();
                for(int i=pos; i<len; i++){
                    if(" ".equals(nsql.substring(i,i+1))){
                        rkey=nsql.substring(pos, i).trim();
                        break;
                    }
                }
                if(rkey!=null){
                    nsql=nsql.replace(rkey, "");
                    rkey=null;
                }
            }
            pos=nsql.indexOf(key);
        }
        nsql=nsql.replace("?", "null");
        return nsql;
    }

    public static String getWhereKey(String sql, int pos){
        if(pos==-1){
            String key=leftStr(SqlNodeVar.getWhereStr(), SqlNodeVar.getWhereStr().length()-1);
            pos=sql.indexOf(key);
        }
        if(pos==-1){
            return null;
        }
        sql=sql+" ";
        int len=sql.length();
        for(int i=pos; i<len; i++){
            String s=sql.substring(i,i+1);
            if(i!=pos && "@".equals(s)){
                return sql.substring(pos, i+1).trim();
            }
        }
        return null;
    }


}
